const mysql = require('mysql')
const config = {
    host: 'localhost',
    port: '3306',
    user: 'test',
    password: 'test',
    database: 'test'
}
const connection = mysql.createConnection(config)

function query(sql,values,callback){
    connection.connect((err) => {
        //if (err) throw err
        if (!err) {
            console.log('连接mysql成功')
        }
    });
    connection.query(sql, values,(err, data, field) => {
        console.log('query:'+sql)
        if (err) console.log(err.message); 
        callback(err,data)
    })
    connection.end();
}
function mydb(name){
    table = name
    sFields = "SELECT *";
    sWhere = "";
    sOrder = "";
    sLimit = ""; 
    sTable=" FROM "+ table +" "; 
    field= function(name = '*'){		
		sFields = "SELECT "+ name;
		return this;	
	}
	where= function(name){		
		sWhere = " WHERE "+ name;
		return this;	
	}
	order= function(name){	
		if(name.match('/.*?(ASC|DESC)\s*?/i'))sOrder = " ORDER BY "+ name;
		else sOrder=" ORDER BY "+ name + " ASC";
		return this;	
	}
	limit= function(val,ext=''){		
		sLimit =" LIMIT "+ val + (ext? "," + ext : '');		
		return this;	
	}
    selsql= function(count=false){
        if(count)return "SELECT COUNT(*) AS total " + sTable + sWhere;		
		return sFields + sTable + sWhere + sOrder + sLimit;
    }
    select = function(values,callback){  
        let sql = selsql();// 'SELECT * FROM '+ table + ' WHERE id = ?'
        return query(sql,values,callback);
    };
    insert = function(values,callback){ 
        let sql = 'INSERT INTO '+ table + ' SET ?'
        console.log('insert:'+sql)
        return query(sql,values,callback); 
    }
     update = function(values,callback){ 
        let sql = 'UPDATE '+ table + ' SET ? '  + swhere;
        return query(sql,values,callback);
    }
    remove = function(values,callback){ 
        let sql = 'DELETE FROM '+ table  + swhere; 
        return query(sql,values,callback);
    }
    return {select,insert,update,remove,field,where,order,limit,selsql}
}
module.exports = mydb
